﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Ccit.ClassLibrary;
using Ccit.Utils;
using Ccit.SysPlat.Log;
using System.Text;
using System.Data;

namespace Ccit.SysPlat.Organ
{
    /// <summary>
    /// Description: 系统平台组织机构 -> 级别管理SQL SERVER数据实现层
    /// Author: tim 2010-02-23
    /// </summary>
    public class ClassSQL : IClass
    {
        #region init error log interface
        private ILog _logInterface = null;
        private const string _funcPageName = "级别管理";
        public ClassSQL()
        {
            _logInterface = Factory.CreateObject(MsErrLog.assemblyName, MsErrLog.className, 
                                                    ImportComponentType.DotNetDll, null) as ILog;
        }
        private string _multiDeptSign = HttpContext.Current.Application["MultiDept"].ToString();  //多部门多职位系统开关
        #endregion

        #region IClass 成员

        public string GetClassNamesByIds(string clsIds, string separator)
        {
            if (string.IsNullOrEmpty(clsIds)) return string.Empty;

            string cmdText = "SELECT SYS_A5_20 AS CLSNAME FROM SYS_A5 WHERE SYS_A5_10 IN (" + clsIds + ")";
           
            string clsNames = string.Empty;
            try
            {
                string clsName = string.Empty;
                using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
                {
                    while (iReader.Read())
                    {
                        clsName = iReader["CLSNAME"].ToString();
                        clsNames += (string.IsNullOrEmpty(clsNames)) ? clsName : separator + clsName;
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            return clsNames;
        }

        public string GetClassNamesByIds(string clsIds, string separator, eTrueFalse isFilter)
        {
            if (string.IsNullOrEmpty(clsIds)) return string.Empty;

            string clsNames = string.Empty;
            if (isFilter == eTrueFalse.TRUE)
            {
                string cmdText = "SELECT SYS_A5_20 AS CLSNAME FROM SYS_A5 WHERE SYS_A5_10 IN (" + clsIds + ") AND SYS_A5_60='0'";
               
                try
                {
                    string clsName = string.Empty;
                    using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
                    {
                        while (iReader.Read())
                        {
                            clsName = iReader["CLSNAME"].ToString();
                            clsNames += (string.IsNullOrEmpty(clsNames)) ? clsName : separator + clsName;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MsErrLog msErrorLog = new MsErrLog
                    {
                        funcPageName = _funcPageName,
                        errMsg = ex.Message,
                        codeTrack = ex.StackTrace,
                        errClassCode = "001", //SQL 异常
                        paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                    };

                    _logInterface.WriteErrorLog(msErrorLog);
                    msErrorLog = null;
                }
            }
            else {
                clsNames = GetClassNamesByIds(clsIds, separator);
            }
            return clsNames;
        }

        public List<MsKeyValue> GetClsListByIds(string clsIds, string findClsName)
        {
            if (string.IsNullOrEmpty(clsIds)) return new List<MsKeyValue>();

            string cmdText = "SELECT SYS_A5_10 AS CLSID,SYS_A5_20 AS CLSNAME FROM SYS_A5 WHERE 1=1 AND SYS_A5_10 IN (" + clsIds + ")";
            if (!string.IsNullOrEmpty(findClsName))
                cmdText += " AND SYS_A5_20 LIKE '%" + findClsName + "%'";

            List<MsKeyValue> lstCls = new List<MsKeyValue>();
            try {
                MsKeyValue msCls;
                using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
                {
                    while (iReader.Read())
                    {
                        msCls = new MsKeyValue {
                            key = iReader["CLSID"].ToString(),
                            val = iReader["CLSNAME"].ToString()
                        };
                        lstCls.Add(msCls);
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            return lstCls;
        }

        public List<MsKeyValue> GetClsListByOrgan(string organId, string filterClsIds,string findClsName)
        {
            StringBuilder sbCmdText = new StringBuilder();
            string whereClause = string.Empty;
            if (!string.IsNullOrEmpty(filterClsIds)) {
                whereClause = " AND A.SYS_A5_10 NOT IN (" + filterClsIds + ")";
            }

            if (!string.IsNullOrEmpty(findClsName)) {
                whereClause += " AND A.SYS_A5_20 LIKE '%" + findClsName + "%'";
            }

            sbCmdText.Append("SELECT A.SYS_A5_10 AS CLSID,A.SYS_A5_20 AS CLSNAME ");
            sbCmdText.Append("FROM SYS_A5 A INNER JOIN SYS_R7 B ON A.SYS_A5_10=B.SYS_R7_20 ");
            sbCmdText.Append("WHERE B.SYS_R7_10='").Append(organId).Append("' AND A.SYS_A5_60='0'").Append(whereClause);
            sbCmdText.Append(" ORDER BY A.SYS_A5_50 ASC");

            List<MsKeyValue> lstClass = new List<MsKeyValue>();
            try
            {
                MsKeyValue msClass = null;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        msClass = new MsKeyValue
                        {
                            key = iReader["CLSID"].ToString(),
                            val = iReader["CLSNAME"].ToString()
                        };
                        lstClass.Add(msClass);
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return lstClass;
        }
        
        public List<string> Delete(string clsId)
        {
            List<string> lstCmdText = new List<string>();
            string cmdText = string.Empty;

            cmdText = "UPDATE SYS_A5 SET SYS_A5_60='1' WHERE SYS_A5_10='" + clsId + "'";
            lstCmdText.Add(cmdText);

            cmdText = "DELETE FROM SYS_R8 WHERE SYS_R8_20='" + clsId + "'";
            lstCmdText.Add(cmdText);

            cmdText = "DELETE FROM SYS_R7 WHERE SYS_R7_20='" + clsId + "'";
            lstCmdText.Add(cmdText);

            try {
                int iexe = DataAccessor.ExecuteNonQuery(lstCmdText);
                if (iexe > 0) return new List<string> { "1", "删除成功!" };
                else return new List<string> { "-1", "操作失败:服务器忙请稍后再试!" };
            }
            catch (Exception ex)
            {
                StringBuilder sbParam = new StringBuilder();
                int sqlIndex = 1;
                foreach (string eSQL in lstCmdText)
                {
                    sbParam.Append("<SQL" + sqlIndex.ToString()).Append("><![CDATA[").Append(eSQL);
                    sbParam.Append("]]></SQL" + sqlIndex.ToString() + ">");
                }
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = sbParam.ToString()
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                sbParam = null;
                return new List<string> { "-2", "操作失败:详情请参见错误日志!" };
            }
        }

        public MsClass GetClsInfo(string clsId)
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT A.SYS_A5_10 AS CLSID,A.SYS_A5_20 AS CLSNAME,A.SYS_A5_30 AS CLSDEGREE,A.SYS_A5_40 AS CLSDESC,");
            sbCmdText.Append("A.SYS_A5_50 AS ORDERNO,A.SYS_A5_60 AS EFFECT,");
            sbCmdText.Append("D.SYS_A4_10 AS ORGID,D.SYS_A4_20 AS ORGNAME,D.SYS_A4_30 AS ORGCODE,D.SYS_A4_40 AS ORGLAYCODE,");
            sbCmdText.Append("D.SYS_A4_50 AS ORGDESC,D.SYS_A4_60 AS ORGORDERNO,D.SYS_A4_70 AS ORGEFFECTSIGN ");
            sbCmdText.Append("FROM SYS_A5 A ");
            sbCmdText.Append("INNER JOIN SYS_R7 C ON A.SYS_A5_10=C.SYS_R7_20 ");
            sbCmdText.Append("INNER JOIN SYS_A4 D ON C.SYS_R7_10=D.SYS_A4_10 ");
            sbCmdText.Append("WHERE A.SYS_A5_10='").Append(clsId).Append("'");

            MsClass msCls = null;
            try
            {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0])
                    {
                        msCls = new MsClass
                        {
                            clsId = iReader["CLSID"].ToString(),
                            clsName = iReader["CLSNAME"].ToString(),
                            degree = iReader["CLSDEGREE"].ToString(),
                            clsDesc = iReader["CLSDESC"].ToString(),
                            orderNo = iReader["ORDERNO"].ToString(),
                            delSign = (eTrueFalse)Enum.Parse(typeof(eTrueFalse), iReader["EFFECT"].ToString()),                           
                            belongOrgan = new MsOrgan {
                                organId = iReader["ORGID"].ToString(),
                                organName = iReader["ORGNAME"].ToString(),
                                organCode = iReader["ORGCODE"].ToString(),
                                layCode = iReader["ORGLAYCODE"].ToString(),
                                organDesc = iReader["ORGDESC"].ToString(),
                                orderNo = iReader["ORGORDERNO"].ToString(),
                                effectSign = iReader["ORGEFFECTSIGN"].ToString()
                            }
                        };
                    }
                }

                sbCmdText.Remove(0, sbCmdText.ToString().Length);
                if (null == msCls) return null;

                //获取级别下用户信息
                sbCmdText.Append("SELECT SYS_A1_10 AS USERID,SYS_A1_40 AS USERNAME FROM SYS_A1 WHERE SYS_A1_10 IN(");
                sbCmdText.Append("SELECT SYS_R8_10 FROM SYS_R8 WHERE SYS_R8_20='").Append(clsId).Append("') AND SYS_A1_90='0'");                
                string userId = string.Empty;
                string userIds = string.Empty;
                string userName = string.Empty;
                string userNames = string.Empty;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        userId = iReader["USERID"].ToString();
                        userName = iReader["USERNAME"].ToString();
                        userIds += (string.IsNullOrEmpty(userIds)) ? userId : "," + userId;
                        userNames += (string.IsNullOrEmpty(userName)) ? userName : " " + userName;
                    }
                }

                msCls.includeUsers = new MsKeyValue {
                    key = userIds,
                    val = userNames
                };

            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return msCls;
        }

        public List<string> IsExistClsName(string clsId, string clsName, string organId)
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT SYS_A5_10 AS CLSID FROM SYS_A5 WHERE SYS_A5_20='");
            sbCmdText.Append(clsName.Replace("'", "''")).Append("' AND SYS_A5_10 IN (SELECT SYS_R7_20 FROM SYS_R7 ");
            sbCmdText.Append("WHERE SYS_R7_10='").Append(organId).Append("')");

            try
            {
                string clsRecId = string.Empty;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0])
                    {
                        clsRecId = iReader["CLSID"].ToString();
                    }
                }

                if (string.IsNullOrEmpty(clsRecId)) return new List<string> { "NOEXIST", "级别不存在!" };
                else
                {
                    if (clsRecId.Equals(clsId)) return new List<string> { "NOEXIST", "级别不存在!" };
                    else return new List<string> { "EXIST", "级别已存在!" };
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return new List<string> { "ERROR", "校验失败:详情请参见错误日志!" };
            }
        }

        public List<string> Save(MsClass msCls)
        {
            string clsId = msCls.clsId;
            if (string.IsNullOrEmpty(clsId)) return addnew(msCls);
            else return update(msCls);
        }

        #endregion


        #region private methods

        private List<string> addnew(MsClass msCls)
        {
            List<string> lstCmdText = new List<string>();
            StringBuilder sbCmdText = new StringBuilder();
            string cmdText = string.Empty;

            #region 保存级别信息

            sbCmdText.Append("INSERT INTO SYS_A5 (SYS_A5_10,SYS_A5_20,SYS_A5_30,SYS_A5_40,SYS_A5_50,SYS_A5_60) VALUES(");
            string newOrderNo = (string.IsNullOrEmpty(msCls.orderNo)) ? DataInterface.GetMaxId("SYS_A5", "SYS_A5_50").ToString() : msCls.orderNo;
            int newClsId = DataInterface.GetMaxId("SYS_A5", "SYS_A5_10");
            sbCmdText.Append("'").Append(newClsId.ToString()).Append("',");
            sbCmdText.Append("'").Append(msCls.clsName.Replace("'", "''")).Append("',");
            sbCmdText.Append("'',"); //级别等级暂时空缺
            sbCmdText.Append("'").Append(msCls.clsDesc.Replace("'", "''")).Append("',");
            sbCmdText.Append("'").Append(newOrderNo).Append("','0')");
            lstCmdText.Add(sbCmdText.ToString());
            sbCmdText.Remove(0, sbCmdText.ToString().Length);
            #endregion

            #region 保存用户级别关系
            string userIds = msCls.includeUsers.key;
            if (!string.IsNullOrEmpty(userIds))
            {
                cmdText = "DELETE FROM SYS_R8 WHERE SYS_R8_10 IN (" + userIds + ")";
                lstCmdText.Add(cmdText);

                string[] userArray = userIds.Split(',');
                foreach (string eUserId in userArray)
                {
                    cmdText = "INSERT INTO SYS_R8(SYS_R8_10,SYS_R8_20) VALUES(";
                    cmdText += "'" + eUserId + "','" + newClsId + "')";
                    lstCmdText.Add(cmdText);
                }
            }
            #endregion

            #region 保存机构级别关系

            string organId = msCls.belongOrgan.organId;
            cmdText = "INSERT INTO SYS_R7(SYS_R7_10,SYS_R7_20) VALUES(";
            cmdText += "'" + organId + "','" + newClsId + "')";
            lstCmdText.Add(cmdText);
            #endregion

            try {
                int iexe = DataAccessor.ExecuteNonQuery(lstCmdText);
                if (iexe > 0) return new List<string> { newClsId.ToString(), "保存成功!" };
                else return new List<string> { "-1", "保存失败:服务器忙请稍后重新尝试操作!" };
            }
            catch (Exception ex)
            {
                StringBuilder sbParam = new StringBuilder();
                int sqlIndex = 1;
                foreach (string eSQL in lstCmdText)
                {
                    sbParam.Append("<SQL" + sqlIndex.ToString()).Append("><![CDATA[").Append(eSQL);
                    sbParam.Append("]]></SQL" + sqlIndex.ToString() + ">");
                }
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = sbParam.ToString()
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                sbParam = null;
                return new List<string> { "-2", "操作失败:详情请参见错误日志!" };
            }

        }


        private List<string> update(MsClass msCls)
        {
            List<string> lstCmdText = new List<string>();
            StringBuilder sbCmdText = new StringBuilder();
            string cmdText = string.Empty;
            string clsId = msCls.clsId;

            #region 更新级别信息
            sbCmdText.Append("UPDATE SYS_A5 SET SYS_A5_20='").Append(msCls.clsName.Replace("'", "''")).Append("'");
            sbCmdText.Append(",SYS_A5_40='").Append(msCls.clsDesc.Replace("'", "''")).Append("'");
            if (!string.IsNullOrEmpty(msCls.orderNo))
                sbCmdText.Append(",SYS_A5_50='").Append(msCls.orderNo).Append("'");
            sbCmdText.Append(" WHERE SYS_A5_10='").Append(clsId).Append("'");
            lstCmdText.Add(sbCmdText.ToString());
            sbCmdText.Remove(0, sbCmdText.ToString().Length);
            #endregion

            #region 更新用户级别关系
            cmdText = "DELETE FROM SYS_R8 WHERE SYS_R8_20='" + clsId + "'";
            lstCmdText.Add(cmdText);

            string userIds = msCls.includeUsers.key;
            if (!string.IsNullOrEmpty(userIds))
            {
                cmdText = "DELETE FROM SYS_R8 WHERE SYS_R8_10 IN (" + userIds + ")";
                lstCmdText.Add(cmdText);

                string[] userArray = userIds.Split(',');
                foreach (string eUserId in userArray)
                {
                    cmdText = "INSERT INTO SYS_R8(SYS_R8_10,SYS_R8_20) VALUES(";
                    cmdText += "'" + eUserId + "','" + clsId + "')";
                    lstCmdText.Add(cmdText);
                }
            }

            #endregion

            try {
                int iexe = DataAccessor.ExecuteNonQuery(lstCmdText);
                if (iexe > 0) return new List<string> { clsId, "保存成功!" };
                else return new List<string> { "-1", "保存失败:服务器忙请稍后重新尝试操作!" };
            }
            catch (Exception ex)
            {
                StringBuilder sbParam = new StringBuilder();
                int sqlIndex = 1;
                foreach (string eSQL in lstCmdText)
                {
                    sbParam.Append("<SQL" + sqlIndex.ToString()).Append("><![CDATA[").Append(eSQL);
                    sbParam.Append("]]></SQL" + sqlIndex.ToString() + ">");
                }
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = sbParam.ToString()
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                sbParam = null;
                return new List<string> { "-2", "操作失败:详情请参见错误日志!" };
            }
        }
        #endregion

    }
}
